package org.jflame.commons.excel;

import org.jflame.commons.excel.handler.ArrayRowReader;
import org.jflame.commons.excel.handler.EntityRowReader;
import org.jflame.commons.excel.validator.DefaultExcelValidator;
import org.jflame.commons.excel.validator.ExcelValidationException;
import org.jflame.commons.excel.validator.IExcelValidator;
import org.jflame.commons.file.FileHelper;
import org.jflame.commons.util.CollectionHelper;
import org.jflame.commons.util.IOHelper;

import java.io.Closeable;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.nio.file.Path;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Optional;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * excel数据导入工具类 ，导入数据转为对应实体类集合或Object[]集体.导入过程可指定数据验证规则 示例: 示例1:
 * 
 * <pre>
 * 
 * try (ExcelImportor xlsImport = new ExcelImportor()) {
 *     xlsImport.setStartRowIndex(1);
 *     try {
 *         LinkedHashSet&lt;Pet&gt; results = xlsImport.importSheet(sheet1, Pet.class);
 *     } catch (ExcelAccessException e) {
 *         xlsImport.getErrorMap();// 错误信息
 *     }
 *     List&lt;Integer&gt; resultIndexs = xlsImport.getCurRowIndexs();
 * }
 * </pre>
 * 
 * @see ExcelColumn
 * @author zyc
 */
public class ExcelImportor implements Closeable {

    private final Logger log = LoggerFactory.getLogger(ExcelImportor.class);

    private boolean allowDuplicate = true;// 是否允许重复数据
    private int startRowIndex = 1;
    private Workbook workbook;
    private OPCPackage pkg;

    public ExcelImportor(Path excelPath) {
        this(excelPath.toFile());
    }

    /**
     * 构造函数
     * 
     * @param excelFile excel文件路径
     * @throws ExcelAccessException
     */
    public ExcelImportor(String excelFile) throws ExcelAccessException {
        this(new File(excelFile));
    }

    /**
     * 构造函数
     * 
     * @param excelFile excel文件
     * @throws ExcelAccessException
     */
    public ExcelImportor(File excelFile) throws ExcelAccessException {
        String ext = FileHelper.getExtension(excelFile.getName(), false);
        try {
            if ("xls".equals(ext)) {
                workbook = new HSSFWorkbook(new FileInputStream(excelFile));
            } else {
                pkg = OPCPackage.open(excelFile, PackageAccess.READ);
                workbook = new XSSFWorkbook(pkg);
            }
        } catch (IOException | InvalidFormatException e) {
            throw new ExcelAccessException("未能正确读取excel文件:" + excelFile, e);
        }
        workbook.setForceFormulaRecalculation(true);
    }

    /**
     * 构造函数
     * 
     * @param stream excel文件流
     * @param isOfficeOpenxml 是否是office openxml格式
     * @throws ExcelAccessException
     */
    public ExcelImportor(InputStream stream, boolean isOfficeOpenxml) throws ExcelAccessException {
        try {
            if (isOfficeOpenxml) {
                pkg = OPCPackage.open(stream);
                workbook = new XSSFWorkbook(pkg);
            } else {
                workbook = new HSSFWorkbook(stream);
            }
        } catch (IOException | InvalidFormatException e) {
            throw new ExcelAccessException("读取excel流异常", e);
        }
        workbook.setForceFormulaRecalculation(true);
    }

    /**
     * 导入指定的excel工作表数据,并转换为相应的对象集合.从第一张sheet导入
     * 
     * @param dataClass
     * @return
     */
    public <T> List<T> importSheet(final Class<T> dataClass) throws ExcelAccessException {
        return importSheet(workbook.getSheetAt(0), dataClass, null);
    }

    /**
     * 导入指定的excel工作表数据,并转换为相应的对象集合.从第一张sheet导入
     * 
     * @param dataClass
     * @param validator 自定义验证器
     * @return
     */
    public <T> List<T> importSheet(final Class<T> dataClass, IExcelValidator<T> validator) throws ExcelAccessException {
        return importSheet(workbook.getSheetAt(0), dataClass, validator);
    }

    /**
     * 导入指定的excel工作表数据,并转换为相应的对象集合. 要转换属性及顺序由dataClass的excelColumn注解决定. <br>
     * 使用默认的验证器验证.使用默认单行数据转换器
     * 
     * @param sheet 工作表
     * @param dataClass 转换类型
     * @param <T> dataClass 泛型类型
     * @exception ExcelAccessException
     * @return
     */
    public <T> List<T> importSheet(Sheet sheet, final Class<T> dataClass) throws ExcelAccessException {
        return importSheet(sheet, dataClass, null);
    }

    /**
     * 导入excel工作表数据转换为指定类型的对象集合.
     * 
     * @param sheet 工作表,null时导入第一张sheet
     * @param dataClass 转换类型class
     * @param validator 指定数据验证类,为null使用DefaultValidator验证
     * @param <T> IExcelEntity
     * @exception ExcelAccessException 导入出错
     * @exception ExcelValidationException 单元格数据验证异常
     * @return
     * @see DefaultExcelValidator
     */
    public <T> List<T> importSheet(Sheet sheet, final Class<T> dataClass, IExcelValidator<T> validator)
            throws ExcelAccessException, ExcelValidationException {
        if (validator == null) {
            validator = new DefaultExcelValidator<T>();
        }

        List<ExcelColumnProperty> lstDescriptors = ExcelColumnProperty.resolveColumnProperty(dataClass, false,
                Optional.empty(), workbook);
        if (CollectionHelper.isEmpty(lstDescriptors)) {
            throw new ExcelAccessException(
                    "No field with @ExcelColumn annotation was found in the " + dataClass.getName());
        }

        EntityRowReader<T> rowHandler = new EntityRowReader<>(lstDescriptors, dataClass);
        Sheet currentSheet = sheet == null ? workbook.getSheetAt(0) : sheet;

        // curRowIndexs.clear();
        Row curRow;
        T newObj = null;
        List<T> results = new ArrayList<>(sheet.getLastRowNum());

        Iterator<Row> rowIterator = currentSheet.rowIterator();
        while (rowIterator.hasNext()) {
            curRow = rowIterator.next();
            if (curRow.getRowNum() < startRowIndex) {
                continue;
            }
            newObj = rowHandler.extractRow(curRow);
            // 验证
            validator.valid(newObj, curRow.getRowNum());

            if (!allowDuplicate) {
                if (results.contains(newObj)) {
                    log.error("重复数据停止导入,行数:{},对象:{}", (curRow.getRowNum() + 1), newObj);
                    throw new ExcelValidationException("重复数据 第" + (curRow.getRowNum() + 1) + "行");
                }
            }
            results.add(newObj);
        }

        return results;
    }

    /**
     * 导入指定的excel工作表数据,转换为数组列表 数组元素类型根据CellType获取
     * 
     * @param sheet excel sheet
     * @return Object[]列表
     */
    public List<Object[]> importSheet(final Sheet sheet) throws ExcelAccessException {
        List<Object[]> results = new ArrayList<>();
        ArrayRowReader rowHandler = new ArrayRowReader();
        Row curRow;
        Iterator<Row> rowIterator = sheet.rowIterator();
        while (rowIterator.hasNext()) {
            curRow = rowIterator.next();
            if (curRow.getRowNum() < startRowIndex) {
                continue;
            }
            results.add(rowHandler.extractRow(curRow));
        }
        return results;
    }

    public List<Object[]> importSheet() throws ExcelAccessException {
        return importSheet(workbook.getSheetAt(0));
    }

    public int getStartRowIndex() {
        return startRowIndex;
    }

    /**
     * 设置从第几行开始导入,行索引从1开始.
     * 
     * @param startRowIndex 开始sheet行索引
     */
    public void setStartRowIndex(int startRowIndex) {
        this.startRowIndex = startRowIndex;
    }

    public Workbook getWorkbook() {
        return workbook;
    }

    public Sheet getSheet(int index) {
        return workbook.getSheetAt(index);
    }

    public Sheet getSheet(String sheetName) {
        return workbook.getSheet(sheetName);
    }

    /**
     * 设置是否允许重复数据,数据重复使用对象equals方法比较,所以如果需要去重导入对象类型应重写equals
     * 
     * @param ignoreRepeat true=允许
     */
    public void setAllowDuplicate(boolean ignoreRepeat) {
        this.allowDuplicate = ignoreRepeat;
    }

    @Override
    public void close() {
        if (pkg != null) {
            try {
                pkg.close();
            } catch (IOException e) {
                pkg = null;
            }
        }
        if (workbook != null) {
            IOHelper.closeQuietly(workbook);
        }
    }

    /**
     * 读取excel文件,将数据解析为指定类型的集合
     * 
     * @param <T>
     * @param excelFile 要读取的excel文件
     * @param dataClass 集合元素类型,带有{@code @ExcelColumn }属性的类型
     * @param validator 数据验证器,可为Null
     * @return
     * @throws ExcelAccessException
     * @see ExcelColumn
     */
    public static <T> List<T> parse(final File excelFile, final Class<T> dataClass, IExcelValidator<T> validator)
            throws ExcelAccessException {
        try (ExcelImportor importor = new ExcelImportor(excelFile)) {
            return importor.importSheet(dataClass, validator);
        }
    }

    /**
     * 读取excel文件,将第一个sheet数据解析为指定类型的集合.
     * 
     * @param <T>
     * @param excelFile 要读取的excel文件
     * @param dataClass 集合元素类型,带有{@code @ExcelColumn }属性的类型
     * @return
     * @throws ExcelAccessException
     * @see ExcelColumn
     */
    public static <T> List<T> parse(final File excelFile, final Class<T> dataClass) throws ExcelAccessException {
        try (ExcelImportor importor = new ExcelImportor(excelFile)) {
            return importor.importSheet(dataClass, null);
        }
    }

    /**
     * 读取excel文件,将第一个sheet数据解析为指定类型的集合.
     * 
     * @param <T>
     * @param excelFile 要读取的excel文件路径
     * @param dataClass 集合元素类型,带有{@code @ExcelColumn }属性的类型
     * @return
     * @throws ExcelAccessException
     * @see ExcelColumn
     */
    public static <T> List<T> parse(final String excelFile, final Class<T> dataClass) throws ExcelAccessException {
        try (ExcelImportor importor = new ExcelImportor(excelFile)) {
            return importor.importSheet(dataClass, null);
        }
    }

    /**
     * 读取excel文件,将第一个sheet数据解析为Object[]集合.
     * 
     * @param excelFile
     * @return
     * @throws ExcelAccessException
     */
    public static List<Object[]> parse(final String excelFile) throws ExcelAccessException {
        try (ExcelImportor importor = new ExcelImportor(excelFile)) {
            return importor.importSheet();
        }
    }
}
